package com.cryptoneedle.center.source.enums;

import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.Properties;

/**
 * <p>description:  </p>
 *
 * @author CryptoNeedle
 * @date 2025-06-23
 */
@Getter
@AllArgsConstructor
public class Oracle extends Database {
    
    public final String jdbcDriverClass = "oracle.jdbc.driver.OracleDriver";
    
    private final String jdbcUrlSidTemplate = "jdbc:oracle:thin:@%s:%d:%s";
    
    private final String jdbcUrlServiceNameTemplate = "jdbc:oracle:thin:@%s:%d/%s";
    
    @Override
    public String getJdbcUrlTemplate(ConnectType connectType) {
        return switch (connectType) {
            case SID -> String.format(this.jdbcUrlSidTemplate);
            case SERVICE_NAME -> this.jdbcUrlServiceNameTemplate;
            default -> throw new RuntimeException("Oracle connection type not supported for Default");
        };
    }
    
    @Override
    public Properties getProperties() {
        Properties properties = new Properties();
        properties.setProperty("oracle.jdbc.timezoneAsRegion", "false");
        return properties;
    }
    
    @Override
    public String getDatabaseValidSql() {
        return "SELECT 1 FROM DUAL";
    }
    
    @Override
    public String getDatabaseVersionSql() {
        return "SELECT BANNER AS version FROM v$version WHERE BANNER LIKE 'Oracle%'";
    }
    
    @Override
    public String getDatabaseStatisticSql() {
        return """
                SELECT owner                                                         AS sourceDatabaseName
                     , SUM(CASE WHEN object_type = 'TABLE' THEN 1 ELSE 0 END)        AS statisticTableCount
                     , SUM(CASE WHEN object_type = 'VIEW' THEN 1 ELSE 0 END)         AS statisticViewCount
                     , (SELECT COUNT(*) FROM all_tab_columns WHERE owner = ao.owner) AS statisticColumnCount
                     , (SELECT COUNT(*) FROM all_constraints WHERE owner = ao.owner) AS statisticConstraintCount
                     , (SELECT COUNT(*) FROM all_indexes WHERE owner = ao.owner)     AS statisticIndexCount
                  FROM all_objects ao
                 WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'APPQOSSYS', 'DBSFWUSER', 'DIP',
                                     'ORACLE_OCM', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'XS$NULL',
                                     'CTXSYS', 'LBACSYS', 'OLAPSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS',
                                     'MDSYS', 'WMSYS', 'EXFSYS', 'FLOWS_FILES', 'OJVMSYS', 'SQLTXPLAIN',
                                     'SI_INFORMTN_SCHEMA', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'DMSYS', 'TSMSYS', 'XDB',
                                     'ANONYMOUS', 'PERFSTAT', 'TRACESVR', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
                                     'MDDATA', 'SYSMAN', 'APEX_PUBLIC_USER', 'OWBSYS', 'OWBSYS_AUDIT')
                   AND owner NOT LIKE 'APEX%'
                   AND owner NOT LIKE 'FLOWS_%'
                 GROUP BY owner
                 ORDER BY owner
                """;
    }
    
    @Override
    public String getTableSql(String sourceDatabaseName) {
        return """
                SELECT t.owner         AS sourceDatabaseName      -- 模式名
                     , t.table_name    AS sourceTableName         -- 表名
                     , c.comments      AS sourceTableComment      -- 表注释
                     , t.num_rows      AS sourceStatisticRowCount -- 行数
                     , t.last_analyzed AS sourceStatisticDatetime -- 统计时间
                  FROM all_tables                       t
                           LEFT JOIN all_tab_comments   c ON t.owner = c.owner AND t.table_name = c.table_name AND c.table_type = 'TABLE'
                           LEFT JOIN all_tab_statistics s ON t.owner = s.owner AND t.table_name = s.table_name AND s.partition_name IS NULL
                 WHERE t.owner = '%s'
                 ORDER BY t.owner, t.table_name
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getColumnSql(String sourceDatabaseName) {
        return """
                SELECT cols.table_name                                    AS sourceTableName                -- 表名
                     , cols.column_name                                   AS sourceColumnName               -- 列名
                     , com.comments                                       AS sourceColumnComment            -- 列注释
                     , cols.data_type                                     AS sourceDataType                 -- 列类型
                     , cols.data_length                                   AS sourceLength                   -- 列长度
                     , cols.data_precision                                AS sourcePrecision                -- 列精度
                     , cols.data_scale                                    AS sourceScale                    -- 列标度
                     , cols.column_id                                     AS sourcePosition                 -- 排序
                     , cols.sample_size                                   AS sourceStatisticSampleCount     -- 采样数量
                     , cols.num_nulls                                     AS sourceStatisticNullCount       -- 采样空值数量
                     , cols.num_distinct                                  AS sourceStatisticDistinctCount   -- 采样不同值数量
                     , to_char(cols.density)                              AS sourceStatisticDensity         -- 采样数据密度
                     , cols.low_value                                     AS sourceStatisticMinValue        -- 采样最小值
                     , cols.high_value                                    AS sourceStatisticMaxValue        -- 采样最大值
                     , cols.avg_col_len                                   AS sourceStatisticAvgDataSizeByte -- 平均字节数(非真实字节数)
                     , cols.last_analyzed                                 AS sourceStatisticTime            -- 统计时间
                  FROM all_tab_columns                                   cols
                           LEFT JOIN all_col_comments                    com
                                     ON cols.owner = com.owner AND cols.table_name = com.table_name AND cols.column_name = com.column_name
                 WHERE cols.owner = '%s'
                 ORDER BY cols.table_name, cols.column_id, cols.column_id
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getConstraintPrimarySql(String sourceDatabaseName) {
        return """
                SELECT ac.owner           AS sourceDatabaseName
                     , ac.table_name      AS sourceTableName
                     , ac.constraint_name AS sourceConstraintName
                     , acc.column_name    AS sourceColumnName
                     , acc.position       AS position
                  FROM all_constraints           ac
                           JOIN all_cons_columns acc ON ac.constraint_name = acc.constraint_name AND ac.owner = acc.owner
                 WHERE ac.owner = '%s'
                   AND ac.constraint_type = 'P'
                   AND ac.STATUS = 'ENABLED'
                 ORDER BY ac.owner, ac.table_name, acc.position
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getConstraintUniqueSql(String sourceDatabaseName) {
        return """
                SELECT ac.owner           AS sourceDatabaseName
                     , ac.table_name      AS sourceTableName
                     , ac.constraint_name AS sourceConstraintName
                     , acc.column_name    AS sourceColumnName
                     , acc.position       AS position
                  FROM all_constraints           ac
                           JOIN all_cons_columns acc ON ac.constraint_name = acc.constraint_name AND ac.owner = acc.owner
                 WHERE ac.constraint_type = 'U'
                   AND ac.owner = '%s'
                   AND ac.STATUS = 'ENABLED'
                 ORDER BY ac.owner, ac.table_name, ac.constraint_name, acc.position
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getConstraintForeignSql(String sourceDatabaseName) {
        return """
                SELECT ac.owner                AS sourceDatabaseName          -- 从表所有者
                     , ac.table_name           AS sourceTableName             -- 从表名
                     , ac.constraint_name      AS sourceConstraintName        -- 从表名
                     , acc.column_name         AS sourceColumnName            -- 从表列名
                     , r_constraint.table_name AS foreignTableName      -- 主表名
                     , r_cols.column_name      AS foreignColumnName     -- 主表列名
                     , r_cols.position         AS foreignColumnPosition -- 排序
                     , ac.delete_rule          AS deleteRule            -- 删除规则
                  FROM all_constraints  ac
                     , all_cons_columns acc
                     , all_constraints  r_constraint
                     , all_cons_columns r_cols
                 WHERE ac.constraint_type = 'R'
                   AND ac.constraint_name = acc.constraint_name
                   AND ac.owner = acc.owner
                   AND ac.r_constraint_name = r_constraint.constraint_name
                   AND ac.r_owner = r_constraint.owner
                   AND r_constraint.constraint_name = r_cols.constraint_name
                   AND r_constraint.owner = r_cols.owner
                   AND acc.position = r_cols.position
                   AND ac.owner = '%s'
                   AND ac.STATUS = 'ENABLED'
                   AND r_constraint.STATUS = 'ENABLED'
                 ORDER BY ac.table_name, r_constraint.table_name
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getConstraintCheckSql(String sourceDatabaseName) {
        return """
                SELECT c.owner                                                          AS sourceDatabaseName
                     , c.table_name                                                     AS sourceTableName
                     , c.constraint_name                                                AS sourceConstraintName
                     , c.search_condition                                               AS checkCondition
                     , CASE WHEN c.validated = 'VALIDATED' THEN 'TRUE' ELSE 'FALSE' END AS validated
                  FROM all_constraints c
                 WHERE c.constraint_type = 'C'
                   AND c.owner = '%s'
                   AND c.STATUS = 'ENABLED'
                   AND c.constraint_name NOT LIKE 'SYS_C%%'
                 ORDER BY c.table_name, c.constraint_name
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getIndexUniqueSql(String sourceDatabaseName) {
        return """
                SELECT i.owner            AS sourceDatabaseName
                     , i.table_name       AS sourceTableName
                     , i.index_name       AS sourceIndexName
                     , ic.column_name     AS sourceColumnName
                     , ic.column_position AS position
                  FROM ALL_INDEXES                   i
                           JOIN      ALL_IND_COLUMNS ic ON i.index_name = ic.index_name AND i.owner = ic.index_owner
                           LEFT JOIN ALL_CONSTRAINTS c ON i.index_name = c.index_name AND i.owner = c.owner
                 WHERE i.uniqueness = 'UNIQUE'
                   AND i.OWNER = '%s'
                   AND i.STATUS = 'VALID'
                   AND c.constraint_name IS NULL
                 ORDER BY i.owner, i.table_name, i.index_name, ic.column_position
                """.formatted(sourceDatabaseName);
    }
    
    @Override
    public String getDmlTrackSql(String sourceDatabaseName) {
        return """
                 SELECT t.table_name    AS tableName   -- 表名称
                      , m.timestamp     AS trackTime   -- 跟踪记录时间
                      , m.inserts       AS insertCount -- 自上次统计收集后的插入操作数
                      , m.updates       AS updateCount -- 自上次统计收集后的更新操作数
                      , m.deletes       AS deleteCount -- 自上次统计收集后的删除操作数
                      , m.truncated     AS truncated   -- 表截断
                      , m.drop_segments AS dropCount   -- 表删除
                   FROM all_tables                           t
                            INNER JOIN all_tab_modifications m
                                       ON t.owner = m.table_owner AND t.table_name = m.table_name AND m.partition_name IS NULL
                  WHERE t.owner = '%s'
                """.formatted(sourceDatabaseName);
    }
}